import xlrd,os,codecs,datetime,getopt,sys,json,re
from datetime import date

out_name   = ''

input_excel = ''
output_file = ''
formats     = []
is_tree     = False
tree_sheet_index_name = ''

SQL_TEMPLATE = u'INSERT INTO `{0}` ({1}) VALUES ({2});'

def main():
  global input_excel
  if input_excel == '':
    exn = raw_input('Please input the excel file name with path\n')
  else:
    exn = input_excel

  re_tree = re.compile(r'(\w+):(\w+):(\w+):(\w+):(\w+)')
  relas = []
  if is_tree and tree_sheet_index_name == '':
    while True:
      tree_input = raw_input('Please input the format <parent_table>:<parent_id>:<parent_attr>:<child_table>:<child_fk> or input \'done\' to continue:\n')
      
      if tree_input == 'done':
        break;
      elif re_tree.match(tree_input) is None:
        print 'Invalid input for :    ', tree_input
      else:
        ta = tree_input.split(':')
        relas.append(Relationship(ta[0], ta[1], ta[2], ta[3], ta[4]))
        
  book = xlrd.open_workbook(exn)
  sheets = book.sheets()

  if output_file == '':
    out_file = input_excel
  else:
    out_file = output_file

  tree_items = []

  if is_tree:
    print '[info ] Building tree structure...'
    if tree_sheet_index_name != '':
      index_sheet = book.sheet_by_name(tree_sheet_index_name)
      if index_sheet is None:
        print '[Error] index sheet not found'
        sys.exit(3)
      for r in range(1, index_sheet.nrows):
        row = index_sheet.row(r)
        relas.append(Relationship(row[0].value.strip(), row[1].value.strip(), row[2].value.strip(), row[3].value.strip(), row[4].value.strip()))
    valid_relas = []
    for sheet in sheets:
      if sheet.name == tree_sheet_index_name:
        continue
      tree_items.append(TreeItem(sheet.name))
    for rela in relas:
      for t in tree_items:
        if rela.ptable == t.table:
          tree_parent = t
        elif rela.ctable == t.table:
          tree_child  = t
      if link_list_has_child(tree_child, tree_parent):
        print '[warn ]  Cannot connect parent ', tree_parent.table, ' to child ', tree_child.table
        print '         Please check if there are circular references'
      else:
        tree_parent.nexts.append(tree_child)
        valid_relas.append(rela)

  if formats.count('sql') >= 1:
    print '[info ] Building sql...'
    comb_list = []
    for sheet in sheets:
      if sheet.name == tree_sheet_index_name:
        continue
      generate_sql(sheet, comb_list)
    f = codecs.open(out_file + '.sql', 'w', 'utf-8')
    f.write('\n'.join(comb_list))
    f.close()

  if formats.count('json') >= 1 or formats.count('xml') >= 1:
    if formats.count('json') >= 1:
      print '[info ] Building json...'
    if formats.count('xml') >= 1:
      print '[info ] Building xml ...'
    obj_res = {}
    for sheet in sheets:
      if sheet.name == tree_sheet_index_name:
        continue
      obj_res[sheet.name] = generate_json(sheet)
    if is_tree:
      obj_res = build_tree_object(obj_res, tree_items, valid_relas)
    if formats.count('json') >= 1:
      json_str = json.dumps(obj_res)
      f = codecs.open(out_file + '.json', 'w', 'utf-8')
      f.write(json_str)
      f.close()


def generate_json(sheet):
  ncols = sheet.ncols
  nrows = sheet.nrows

  frow = sheet.row(0)
  fcell = []
  for f in range(0, ncols):
    cell = frow[f]
    v = cell.value.strip()
    if v.startswith('#'):
      ncols = f
      break 
    fcell.append(v) 

  result = []
  for r in range(1, nrows):
    obj = {}
    for c in range(0, ncols):
      obj[fcell[c]] = format_cell_json(sheet.row(r)[c])
    result.append(obj)
  return result

def generate_sql(sheet, icomb_list):
  comb_list.append('\n/**' + sheet.name + '      generation time :  '  + datetime.datetime.now().isoformat(' ') + '*/')
  ncols = sheet.ncols
  nrows = sheet.nrows

  frows = sheet.row(0)
  fcell = []
  for f in range(0, ncols):
    cell = frows[f]
    v = cell.value.strip()
    if v.startswith('#'):
      ncols = f
      break 
    fcell.append('`' + v + '`')

  for r in range(1, nrows):
    clist = []
    for c in range(0, ncols):
      cell = sheet.row(r)[c]
      clist.append(format_cell_sql(cell))
    comb_list.append(SQL_TEMPLATE.format(sheet.name, ','.join(fcell), ' , '.join(clist)))

def get_params():
  global formats
  global input_excel
  global is_tree
  global tree_sheet_index_name
  try:
    opts, args = getopt.getopt(sys.argv[2:],"hi:o:f:t:")
  except getopt.GetoptError:
    print 'excel_extractor.py -i<inputfile> -o<outputfile> -f<json,xml,sql> -t<tree output, index sheet name>'
    sys.exit(2)
  input_excel = sys.argv[1] 
  for opt, arg in opts:
    if opt == '-h':
      print 'excel_extractor.py -i<inputfile> -o<outputfile> -f<json,xml,sql> -t<tree output, index sheet name>'
      sys.exit()
    #elif opt in ('-i'):
     # print arg
     # input_excel = arg
    elif opt in ('-o'):
      output_file = arg
    elif opt == '-f':
      formats = arg.split(',')
    elif opt == '-t':
      is_tree = True
      tree_sheet_index_name = arg

def equal_float(a, b):
  return abs(a - b) <= 0.00000001

def link_list_has_child(parent, child):
  while len(parent.nexts) > 0:
    for n in parent.nexts:
      if n != child:
        res = link_list_has_child(n, child)
      elif n == child:
        return True
  return False

def build_tree_object(sourceobj, tree_items, valid_relas):
  result = {}
  for t1 in tree_items:
    t1_not_parent = False
    for t2 in tree_items:
      for t3 in t2.nexts:
        if t1 == t3:
          t1_not_parent = True
    if t1_not_parent == False:
      result[t1.table] = sourceobj[t1.table]
      for t1obj in result[t1.table]:
        build_child_object(t1obj, t1, sourceobj, valid_relas)
  return result

def build_child_object(pobj, pt, sourceobj, relas):
  for ct in pt.nexts:
    for rela in relas:
      if rela.ptable == pt.table and rela.ctable == ct.table:
        # bingo
        pattrarr = []
        pobj[rela.pattr] = pattrarr
        carr = sourceobj[rela.ctable]
        for cobj in carr:
          if cobj[rela.cfk] == pobj[rela.pid]:
            pattrarr.append(cobj)
            build_child_object(cobj, ct, sourceobj, relas)

def format_cell_sql(cell):
  cval = cell.value
  if cell.ctype == 0:
    cval = 'NULL'
  elif cell.ctype == 1 and cell.value != 'NULL':
    cval = "'" + cval.replace("'", "\\'") + "'"
  elif cell.ctype == 2:
    if equal_float(int(cval), cval):
      cval = str(int(cval))
    else:
      cval = str(cval)
  elif cell.ctype == 3:
    d_tuple = xlrd.xldate_as_tuple(cval, 0)
    cval = str(datetime.datetime(d_tuple[0], d_tuple[1], d_tuple[2],d_tuple[3],d_tuple[4],d_tuple[5]).isoformat(' '))
    cval = "'" + cval + "'"
  return cval

def format_cell_json(cell):
  cval = cell.value
  if cell.ctype == 0:
    cval = ''
  elif cell.ctype == 1 and cell.value == 'NULL':
    cval = ''
  elif cell.ctype == 2:
    if equal_float(int(cval), cval):
      cval = str(int(cval))
    else:
      cval = str(cval)
  elif cell.ctype == 3:
    d_tuple = xlrd.xldate_as_tuple(cval, 0)
    cval = str(datetime.datetime(d_tuple[0], d_tuple[1], d_tuple[2],d_tuple[3],d_tuple[4],d_tuple[5]).isoformat(' '))
    cval = cval
  return cval

class Relationship:
  def __init__(self, ptable, pid, pattr, ctable, cfk):
    self.ptable = ptable
    self.pid    = pid
    self.pattr  = pattr
    self.ctable = ctable
    self.cfk    = cfk

class TreeItem:
  def __init__(self, table):
    self.table = table
    self.nexts = []

if __name__ == '__main__':
  get_params()
  main()
